package com.jiexinyun.utils.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.*;

public class ComplexExcelUtils {

    /**
     * 导出 Excel (2007以上版本 .xlsx)
     */
    public static void exportExcel(HttpServletResponse response,
                                   List<?> dataList,
                                   String fileName,
                                   String sheetName,
                                   Class<?> clazz) throws IOException {
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        // 创建工作簿
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            // 创建工作表
            XSSFSheet sheet = workbook.createSheet(sheetName);

            // 创建表头
            Field[] fields = clazz.getDeclaredFields();
            XSSFRow headerRow = sheet.createRow(0);
            for (int i = 0; i < fields.length; i++) {
                Field field = fields[i];
                ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                String headerName = annotation != null ? annotation.value()[0] : field.getName();
                headerRow.createCell(i).setCellValue(headerName);
            }

            // 填充数据
            for (int i = 0; i < dataList.size(); i++) {
                XSSFRow row = sheet.createRow(i + 1);
                Object obj = dataList.get(i);
                for (int j = 0; j < fields.length; j++) {
                    Field field = fields[j];
                    field.setAccessible(true);
                    Object value = field.get(obj);
                    row.createCell(j).setCellValue(value != null ? value.toString() : "");
                }
            }

            // 写入输出流
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new RuntimeException("导出Excel失败", e);
        }
    }

    /**
     * 导入 Excel
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> clazz) throws IOException {
        List<T> dataList = new ArrayList<>();

        try (InputStream inputStream = file.getInputStream()) {
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(0);

            // 获取字段映射
            Field[] fields = clazz.getDeclaredFields();
            Map<Integer, Field> fieldMap = new HashMap<>();
            XSSFRow headerRow = sheet.getRow(0);
            for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
                String headerName = headerRow.getCell(i).getStringCellValue();
                for (Field field : fields) {
                    ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                    String fieldHeader = annotation != null ? annotation.value()[0] : field.getName();
                    if (fieldHeader.equals(headerName)) {
                        fieldMap.put(i, field);
                        break;
                    }
                }
            }

            // 读取数据
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                XSSFRow row = sheet.getRow(i);
                if (row == null) continue;

                T obj = clazz.getDeclaredConstructor().newInstance();
                for (Map.Entry<Integer, Field> entry : fieldMap.entrySet()) {
                    int cellIndex = entry.getKey();
                    Field field = entry.getValue();
                    XSSFCell cell = row.getCell(cellIndex);

                    field.setAccessible(true);
                    if (cell == null) {
                        field.set(obj, null);
                        continue;
                    }

                    Object value = getCellValue(cell, field.getType());
                    field.set(obj, value);
                }
                dataList.add(obj);
            }
        } catch (Exception e) {
            throw new RuntimeException("导入Excel失败", e);
        }

        return dataList;
    }

    private static Object getCellValue(XSSFCell cell, Class<?> fieldType) {
        switch (cell.getCellType()) {
            case STRING:
                String strValue = cell.getStringCellValue();
                if (fieldType == String.class) return strValue;
                if (fieldType == Integer.class || fieldType == int.class) return Integer.parseInt(strValue);
                if (fieldType == Long.class || fieldType == long.class) return Long.parseLong(strValue);
                if (fieldType == Double.class || fieldType == double.class) return Double.parseDouble(strValue);
                if (fieldType == Boolean.class || fieldType == boolean.class) return Boolean.parseBoolean(strValue);
                if (fieldType == LocalDate.class) return LocalDate.parse(strValue);
                if (fieldType == LocalDateTime.class) return LocalDateTime.parse(strValue);
                return strValue;
            case NUMERIC:
                double numValue = cell.getNumericCellValue();
                if (fieldType == String.class) return String.valueOf(numValue);
                if (fieldType == Integer.class || fieldType == int.class) return (int) numValue;
                if (fieldType == Long.class || fieldType == long.class) return (long) numValue;
                if (fieldType == Double.class || fieldType == double.class) return numValue;
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    if (fieldType == LocalDate.class) return date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
                    if (fieldType == LocalDateTime.class) return date.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
                    return date;
                }
                return numValue;
            case BOOLEAN:
                boolean boolValue = cell.getBooleanCellValue();
                if (fieldType == String.class) return String.valueOf(boolValue);
                return boolValue;
            case FORMULA:
                throw new AssertionError();
            default:
                return null;
        }
    }
}
